﻿using Dapper;
using MediatR;
using MySql.Data.MySqlClient;
using SCM.API.Read.Application.Command.Ledger;
using SCM.Domain;
using SCM.ErrorCode;
using SCM.Extensions.Ledger;
using SCM.Infrastructure.Interface;
using System.Text;

namespace SCM.API.Read.Application.Handler.Ledger
{
    public class GetStockHandler : IRequestHandler<GetStockCommand, APIPaging<StockDTO>>
    {
        IDatabaseContext db;

        public GetStockHandler(IDatabaseContext db)
        {
            this.db = db;
        }

        public async Task<APIPaging<StockDTO>> Handle(GetStockCommand request, CancellationToken cancellationToken)
        {
            var result = new APIPaging<StockDTO>();

            var countSql = new StringBuilder();
            var sqlBuilder = new StringBuilder();

            sqlBuilder.Append(@"SELECT  a.id,
                                        g.ProductId,
                                        g.ProductCode,
                                        g.ProductName,
                                        a.ProductStatus,
                                        b.CategoryId,
                                        b.CategoryName,
                                        g.Specifications,
                                        c.UnitId,
                                        c.UnitName,
                                        a.Barcode,
                                        d.DepotId,
                                        d.DepotName,
                                        e.LocationId,
                                        e.LocationName,
                                        a.StockId,
                                        a.Stocknum,
                                        a.StockMoney,
                                        a.isDel
                                FROM StockModel a JOIN CategoryModel b on a.CategoryId=b.CategoryId 
                                                  JOIN  UnitModel c on a.UnitId=c.UnitId JOIN  DepotModel d on a.DepotId=d.DepotId
                                                  JOIN  LocationModel e on a.LocationId=e.LocationId
                                                  JOIN Stocking f on a.StockId=f.Id
                                                  JOIN ProductModel g on a.ProductId=g.ProductId 
                                WHERE a.IsDel=0 AND b.IsDel=0 AND c.IsDel=0 AND d.IsDel=0 AND e.IsDel=0 AND f.IsDeleted=0 AND g.IsDel=0 ");

            countSql.Append(@"SELECT COUNT(a.id) FROM StockModel a JOIN CategoryModel b on a.CategoryId=b.CategoryId 
                                                JOIN  UnitModel c on a.UnitId=c.UnitId
                                                JOIN  DepotModel d on a.DepotId=d.DepotId
                                                JOIN  LocationModel e on a.LocationId=e.LocationId
                                                JOIN Stocking f on a.StockId=f.Id
                                                JOIN ProductModel g on a.ProductId=g.ProductId 
                                WHERE a.IsDel=0 AND b.IsDel=0 AND c.IsDel=0 AND d.IsDel=0 AND e.IsDel=0 AND f.IsDeleted=0 AND g.IsDel=0 ");
            Console.WriteLine(sqlBuilder.ToString());
            var parameters = new DynamicParameters();

            if (!string.IsNullOrEmpty(request.ProductName))
            {
                sqlBuilder.Append(" AND a.ProductName LIKE @a.ProductName ");
                parameters.Add("a.ProductName", $"%{request.ProductName}%");
                countSql.Append(" AND a.ProductName LIKE @a.ProductName ");
            }
            if (!string.IsNullOrEmpty(request.UnitName))
            {
                sqlBuilder.Append(" AND UnitName LIKE @UnitName ");
                parameters.Add("UnitName", $"%{request.UnitName}%");
                countSql.Append(" AND UnitName LIKE @UnitName ");
            }
            if (!string.IsNullOrEmpty(request.Barcode))
            {
                sqlBuilder.Append(" AND Barcode LIKE @Barcode ");
                parameters.Add("Barcode", $"%{request.Barcode}%");
                countSql.Append(" AND Barcode LIKE @Barcode ");
            }
            if (!string.IsNullOrEmpty(request.DepotName))
            {
                sqlBuilder.Append(" AND d.DepotName LIKE @d.DepotName ");
                parameters.Add("d.DepotName", $"%{request.DepotName}%");
                countSql.Append(" AND d.DepotName LIKE @d.DepotName ");
            }


            sqlBuilder.Append("ORDER BY id DESC ");
            sqlBuilder.Append("LIMIT @PageSize OFFSET @Offset ");
            parameters.Add("PageSize", request.PageSize);
            parameters.Add("Offset", (request.PageIndex - 1) * request.PageSize);

            

            var data = await db.DapperConnection.QueryAsync<StockDTO>(sqlBuilder.ToString(), parameters);
            var total = await db.DapperConnection.ExecuteScalarAsync<int>(countSql.ToString(), parameters);

            result.Msg = "显示成功";
            result.Code = APIEnum.Success;
            result.items = data.ToList();
            result.totalCount = total;
            result.totalPage = (int)Math.Ceiling((double)total / request.PageSize);
            return result;

        }
    }
}
